package com.h2cloud.excel;

/**
 * @Package: com.h2cloud.excel
 * @ClassName: ExcelHeaderMergerWriter
 * @Author: wulongkai
 * @CreateTime: 2023/8/7 15:11
 * @Description:
 */

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import org.apache.poi.ss.usermodel.Sheet;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class ExcelHeaderMergerWriter {

    public static void main(String[] args) {
        String inputFilePath = "/Users/a58/Downloads/nullfail.xlsx";
        String outputFilePath = "/Users/a58/Downloads/file.xlsx";

        List<List<String>> mergedHeaders = mergeHeaders(inputFilePath);
        writeMergedHeadersToFile(outputFilePath, mergedHeaders);
    }

    public static List<List<String>> mergeHeaders(String filePath) {
        List<List<String>> mergedHeaders = new ArrayList<>();
        Map<String, Integer> headerCountMap = new HashMap<>();

        // 创建一个事件监听器，用于处理 Excel 数据
        AnalysisEventListener<List<String>> listener = new AnalysisEventListener<List<String>>() {
            @Override
            public void invoke(List<String> row, AnalysisContext context) {
                // 处理表头行
                if (context.readRowHolder().getRowIndex() == 0) {
                    List<String> mergedHeader = new ArrayList<>();
                    for (String cellValue : row) {
                        int count = headerCountMap.getOrDefault(cellValue, 0);
                        headerCountMap.put(cellValue, count + 1);
                        String mergedCellValue = cellValue;
                        if (count > 0) {
                            mergedCellValue = cellValue + " (" + count + ")";
                        }
                        mergedHeader.add(mergedCellValue);
                    }
                    mergedHeaders.add(mergedHeader);
                }
            }

            @Override
            public void doAfterAllAnalysed(AnalysisContext context) {
                // 数据读取完成后的逻辑处理
            }
        };

        // 使用 EasyExcel 读取 Excel 数据
        EasyExcel.read(filePath, listener).sheet().doRead();

        return mergedHeaders;
    }

    public static void writeMergedHeadersToFile(String filePath, List<List<String>> mergedHeaders) {
        EasyExcel.write(filePath).sheet("Merged Headers").doWrite(mergedHeaders);

        // 设置列宽
//        try {
//            Workbook workbook = (Workbook) EasyExcel.write(filePath).build();
//            Sheet sheet = workbook.getSheetAt(0);
//            Map<Integer, Integer> columnWidthMap = new HashMap<>();
//            int maxColumnIndex = mergedHeaders.get(0).size() - 1;
//            for (int i = 0; i <= maxColumnIndex; i++) {
//                int maxWidth = getMaxColumnWidth(sheet, i);
//                columnWidthMap.put(i, maxWidth);
//            }
//            setColumnWidth(sheet, columnWidthMap);
//            workbook.write(new FileOutputStream(filePath));
//        } catch (IOException e) {
//            e.printStackTrace();
//        }
    }

    public static int getMaxColumnWidth(Sheet sheet, int columnIndex) {
        int maxWidth = 0;
        for (int i = 0; i < sheet.getLastRowNum(); i++) {
            int cellWidth = sheet.getRow(i).getCell(columnIndex).getStringCellValue().getBytes().length;
            if (cellWidth > maxWidth) {
                maxWidth = cellWidth;
            }
        }
        return maxWidth;
    }

    public static void setColumnWidth(Sheet sheet, Map<Integer, Integer> columnWidthMap) {
        for (int columnIndex : columnWidthMap.keySet()) {
            int columnWidth = columnWidthMap.get(columnIndex);
            sheet.setColumnWidth(columnIndex, (columnWidth + 2) * 256); // 设置列宽时，需要将字符宽度乘以256
        }
    }
}



